package ru.arriah.servicedesk.ejb;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.ejb.EJBException;
import javax.ejb.SessionContext;
import javax.sql.DataSource;

import ru.arriah.common.ejb.GenericSessionBean;
import ru.arriah.servicedesk.bean.MailTemplateBean;
import ru.arriah.servicedesk.help.DbUtils;
import ru.arriah.servicedesk.help.Utils;

public class UtilityManager extends GenericSessionBean {
  protected DataSource dataSource;
  
  public UtilityManager() {
    super();
    dataSource = null;      
  }   


  public void setSessionContext(
    SessionContext sessionContext
  ) throws EJBException, RemoteException {
    super.setSessionContext(sessionContext);

    try {
       this.dataSource = Utils.getDataSource();
    } catch (Exception e) {
       handleException(e, "setSessionContext");
       throw new EJBException(e);
    }
  }
  
  
  
  public MailTemplateBean selectMailTemplateByKey(String key) {
    
    Connection connection = null;     
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    
    try {
      MailTemplateBean mailBean = new MailTemplateBean();
      connection = dataSource.getConnection();
      
      preparedStatement = connection.prepareStatement(
        "SELECT * FROM"+
        " `mailtemplate`" +            
        " WHERE `mailtemplate`.`key` = ?"
      );
      
      preparedStatement.setString(1, key);
            
      resultSet = preparedStatement.executeQuery();
      
      if(resultSet.next()) {
        mailBean.setId(
           resultSet.getInt("mailtemplate.id")
        );
        mailBean.setBody(
           resultSet.getString("mailtemplate.body")
        );
        mailBean.setSubject(
           resultSet.getString("mailtemplate.subject")
        );
        mailBean.setKey(
           resultSet.getString("mailtemplate.key")
        );
      }
      
      return mailBean;       
    } catch (Exception e){
       handleException(e, "selectMailTemplateByKey");
       throw new EJBException(e);         
    }  finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
  }
}